package com.useeinfo.framework.sugar.tools;

import com.useeinfo.framework.sugar.data.DataVO;
import com.useeinfo.framework.sugar.data.TitleVO;
import com.useeinfo.framework.sugar.enumerate.FieldType;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.util.*;

/**
 * 报表导出工具类
 *
 * @author JYLiu
 * @巴黎的雨季 本工具是根据POI对Excel2003进行报表导出 本工具类可根据模板进行Excel的导出
 * 并且可根据提供的模板Sheet页进行复制 从而实现多个Sheet页的需求
 * 使用本工具类时,如果需求是每个Sheet页中的数据都不一致,但是表格样式和模板都一样
 * 那么只需要在实际情况中根据 sql 来查询要添加的数据源 (只需更改数据源即可)
 */
public class ExcelUtil {


    public static void test1(HttpServletResponse response) {
        Map<String, Map<String, List<DataVO>>> map1 = new HashMap<>();
        Map<Integer, List<DataVO>> map = new HashMap<>();
        List<DataVO> dataVOS = new ArrayList<DataVO>() {
            {
                for (int i = 4; i <= 15; i++) {
                    add(new DataVO(i, i + "", FieldType.Text, false));
                }
            }
        };
        map.put(4, dataVOS);
        map.put(5, dataVOS);
        map.put(6, dataVOS);

        List<TitleVO> titleVOS = new ArrayList<TitleVO>() {{
            add(new TitleVO(4, "阀门状态与挂牌一致"));
            add(new TitleVO(5, "能源锁定图无缺失"));
            add(new TitleVO(6, "冷却水进水压力"));
            add(new TitleVO(7, "冷却水进水温度"));
            add(new TitleVO(8, "冷却水出水温度"));
            add(new TitleVO(9, "空压机油位"));
            add(new TitleVO(10, "自动排水动作"));
            add(new TitleVO(11, "气量百分比"));
            add(new TitleVO(12, "机主排气温度"));
            add(new TitleVO(13, "进气真空度"));
            add(new TitleVO(14, "油过滤器压差"));
            add(new TitleVO(15, "一级排气温度"));
            add(new TitleVO(16, "二级进气压力"));
            add(new TitleVO(17, "二级进气温度"));
            add(new TitleVO(18, "二级排气温度"));
            add(new TitleVO(19, "二级排气压力"));
            add(new TitleVO(20, "轴承润滑油压"));
            add(new TitleVO(21, "轴承润滑油温"));
            add(new TitleVO(22, "电机频率"));
            add(new TitleVO(23, "电机电流"));
            add(new TitleVO(24, "运行总小时"));
            add(new TitleVO(25, "变频器直电电压"));
        }};
        String modelURLString = "C:\\model\\螺杆式空压机组点检表.xls";
        try {
            ExcelByModel1(response, "测试模板导出", modelURLString, map, titleVOS, "", "");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void ExcelByModel1(HttpServletResponse response, String ExcelName, String ModelURl, Map<Integer, List<DataVO>> map, List<TitleVO> titleVOS, String unit, String selectDate) throws Exception {
        // 设置导出Excel报表的导出形式
        response.setContentType("application/vnd.ms-excel");
        // 设置导出Excel报表的响应文件名
        String fileName = new String(ExcelName.getBytes("utf-8"), "ISO-8859-1");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
        // 创建一个输出流
        OutputStream fileOut = response.getOutputStream();
        // 读取模板文件路径
        File file = new File(ModelURl);
        FileInputStream fins = new FileInputStream(file);
        POIFSFileSystem fs = new POIFSFileSystem(fins);
        // 读取Excel模板
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        // 设置边框样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置边框样式的颜色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 模板页
        HSSFSheet sheetModel = null;
        // 创建行
        HSSFRow row = null;
        // 循环建立Sheet页
        // 读取模板中模板Sheet页中的内容
        sheetModel = wb.getSheetAt(0);
        row = getRow(sheetModel, 1);
        Date date = StringConverters.ToDateOnly(selectDate);
        getCell(row, 6).setCellValue("点检日期：" + DateTimeUtils.format(date, "yyyy") + "年" + DateTimeUtils.format(date, "MM") + "月" + DateTimeUtils.format(date, "dd") + "日");
        getCell(row, 14).setCellValue("机组编号：" + unit);
        //获取到新建Sheet页中的第一行为其中的列赋值
        if (CollectionUtils.isNotEmpty(titleVOS)) {
            for (TitleVO titleVO : titleVOS) {
                row = getRow(sheetModel, titleVO.getI());
                List<DataVO> dataVOS = map.get(titleVO.getI());
                if (CollectionUtils.isNotEmpty(dataVOS)) {
                    for (DataVO dataVO : dataVOS) {
                        if (dataVO.isAbnormal()) {
                            getRedCell(row, dataVO.getI(), wb).setCellValue(dataVO.getValue());
                        } else {
                            getCell(row, dataVO.getI()).setCellValue(dataVO.getValue());
                        }
                    }
                }
            }
        }
        // 写入流
        wb.write(fileOut);
        fileOut.flush();
        // 关闭流
        fileOut.close();
        return;
    }


    /**
     * 根据模板导出报表，可导出多个Sheet页
     *
     * @param ExcelName  导出的Excel文件名
     * @param ModelURl   模板路径 (全路径)
     * @param dataSource 数据源
     * @param response   返回请求
     * @param sheetNames 生成的Sheet页的名称集合
     * @param keyNames   数据源中Map集合的key值 (key值对应的value值顺序要列名顺序一致)
     * @param rowNum     开始 循环写入数据 的行数(从第几行开始写入数据)
     */
    public static void ExcelByModel(String ExcelName, String ModelURl, List<Map<String, String>> dataSource,
                                    HttpServletResponse response, String[] sheetNames, String[] keyNames, int rowNum) throws Exception {
        // 设置导出Excel报表的导出形式
        response.setContentType("application/vnd.ms-excel");
        // 设置导出Excel报表的响应文件名
        String fileName = new String(ExcelName.getBytes("utf-8"), "ISO-8859-1");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
        // 创建一个输出流
        OutputStream fileOut = response.getOutputStream();
        // 读取模板文件路径
        File file = new File(ModelURl);
        FileInputStream fins = new FileInputStream(file);
        POIFSFileSystem fs = new POIFSFileSystem(fins);
        // 读取Excel模板
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        // 设置边框样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置边框样式的颜色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 模板页
        HSSFSheet sheetModel = null;
        // 新建的Sheet页
        HSSFSheet newSheet = null;
        // 创建行
        HSSFRow row = null;
        // 创建列
        HSSFCell cell = null;
        // 循环建立Sheet页
        for (int i = 0; i < sheetNames.length; i++) {
            // 读取模板中模板Sheet页中的内容
            sheetModel = wb.getSheetAt(0);
            // 设置新建Sheet的页名
            newSheet = wb.createSheet(sheetNames[i]);
            // 将模板中的内容复制到新建的Sheet页中
            copySheet(wb, sheetModel, newSheet, sheetModel.getFirstRowNum(), sheetModel.getLastRowNum());
            //获取到新建Sheet页中的第一行为其中的列赋值
            row = getRow(newSheet, 0);
            getCell(row, 1).setCellValue("这是为表代码赋的值");
            //注意 合并的单元格也要按照合并前的格数来算
            getCell(row, 6).setCellValue("这是为外部代码赋的值");
            //获取模板中的第二列，并赋值
            row = getRow(newSheet, 1);
            getCell(row, 1).setCellValue("表名称赋值");
            //注意 合并的单元格也要按照合并前的格数来算
            getCell(row, 6).setCellValue("这是为是否系统表赋的值");
            // 遍历数据源 开始写入数据(因为Excel中是从0开始,所以减一)
            int num = rowNum - 1;
            for (Map<String, String> item : dataSource) {
                // 循环遍历,新建行
                row = newSheet.createRow((short) num);
                //判断有多少列数据
                for (int j = 0; j < keyNames.length; j++) {
                    // 设置每列的数据   设置每列的样式   设置每列的值
                    cell = row.createCell(j);
                    cell.setCellStyle(style);
                    cell.setCellValue(item.get(keyNames[j]));
                }
                num++;
            }
            // break 加break可以测试只添加一个Sheet页的情况
        }
        wb.removeSheetAt(0);
        // 写入流
        wb.write(fileOut);
        fileOut.flush();
        // 关闭流
        fileOut.close();
        return;
    }

    /**
     * @param wb        Excel工作簿对象
     * @param fromsheet 模板Sheet页
     * @param newSheet  新建Sheet页
     * @param firstrow  模板页的第一行
     * @param lasttrow  模板页的最后一行
     */
    private static void copySheet(HSSFWorkbook wb, HSSFSheet fromsheet, HSSFSheet newSheet, int firstrow, int lasttrow) {
        // 复制一个单元格样式到新建单元格
        if ((firstrow == -1) || (lasttrow == -1) || lasttrow < firstrow) {
            return;
        }
        // 复制合并的单元格
        Region region = null;
        for (int i = 0; i < fromsheet.getNumMergedRegions(); i++) {
            region = fromsheet.getMergedRegionAt(i);
            if ((region.getRowFrom() >= firstrow) && (region.getRowTo() <= lasttrow)) {
                newSheet.addMergedRegion(region);
            }
        }
        HSSFRow fromRow = null;
        HSSFRow newRow = null;
        HSSFCell newCell = null;
        HSSFCell fromCell = null;
        // 设置列宽
        for (int i = firstrow; i < lasttrow; i++) {
            fromRow = fromsheet.getRow(i);
            if (fromRow != null) {
                for (int j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
                    int colnum = fromsheet.getColumnWidth((short) j);
                    if (colnum > 100) {
                        newSheet.setColumnWidth((short) j, (short) colnum);
                    }
                    if (colnum == 0) {
                        newSheet.setColumnHidden((short) j, true);
                    } else {
                        newSheet.setColumnHidden((short) j, false);
                    }
                }
                break;
            }
        }
        // 复制行并填充数据
        for (int i = 0; i < lasttrow; i++) {
            fromRow = fromsheet.getRow(i);
            if (fromRow == null) {
                continue;
            }
            newRow = newSheet.createRow(i - firstrow);
            newRow.setHeight(fromRow.getHeight());
            for (int j = fromRow.getFirstCellNum(); j < fromRow.getPhysicalNumberOfCells(); j++) {
                fromCell = fromRow.getCell((short) j);
                if (fromCell == null) {
                    continue;
                }
                newCell = newRow.createCell((short) j);
                newCell.setCellStyle(fromCell.getCellStyle());
                int cType = fromCell.getCellType();
                newCell.setCellType(cType);
                switch (cType) {
                    case HSSFCell.CELL_TYPE_STRING:
                        newCell.setCellValue(fromCell.getRichStringCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        newCell.setCellValue(fromCell.getNumericCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_BLANK:
                        newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA:
                        newCell.setCellValue(fromCell.getCellFormula());
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        newCell.setCellValue(fromCell.getBooleanCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_ERROR:
                        newCell.setCellValue(fromCell.getErrorCellValue());
                        break;
                    default:
                        newCell.setCellValue(fromCell.getRichStringCellValue());
                        break;
                }
            }
        }
    }

    private static HSSFRow getRow(HSSFSheet sheet, int index) {
        if (sheet.getRow(index) == null) {
            sheet.createRow(index);
        }
        return sheet.getRow(index);
    }

    private static HSSFCell getCell(HSSFRow row, int index) {
        if (row.getCell(index) == null) {
            row.createCell(index);
        }
        return row.getCell(index);
    }

    private static HSSFCell getRedCell(HSSFRow row, int index, HSSFWorkbook wb) {
        HSSFCell cell;
        if (row.getCell(index) == null) {
            cell = row.createCell(index);
        } else {
            cell = row.getCell(index);
        }
        cell.setCellStyle(getRedStyle(wb));
        return cell;
    }

    /**
     * 设置表体的单元格样式
     *
     * @return
     */
    public static HSSFCellStyle getRedStyle(HSSFWorkbook wb) {
        // 普通单元格样式
        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setColor(HSSFColor.RED.index);
        style.setFont(font);
        // 设置边框样式
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置边框样式的颜色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        return style;
    }

    public static void test(HttpServletResponse response) {
        //构建数据源
        List<Map<String, String>> dataSourceList = new ArrayList<Map<String, String>>() {
            {
                add(new HashMap<String, String>() {{
                    put("字段编号", "1");
                    put("字段代码", "BUSINESS_ID");
                    put("字段含义", "业务id");
                    put("数据类型", "VARCHAR");
                    put("长度", "64");
                    put("主键", "是");
                    put("主码", "");
                }});
                add(new HashMap<String, String>() {{
                    put("字段编号", "2");
                    put("字段代码", "PROC_INST_ID");
                    put("字段含义", "流程实例编号");
                    put("数据类型", "VARCHAR");
                    put("长度", "64");
                    put("主键", "");
                    put("主码", " ");
                }});
                add(new HashMap<String, String>() {{
                    put("字段编号", "3");
                    put("字段代码", "PROC_STATE");
                    put("字段含义", "流程状态");
                    put("数据类型", "VARCHAR");
                    put("长度", "64");
                    put("主键", " ");
                    put("主码", " ");
                }});
                add(new HashMap<String, String>() {{
                    put("字段编号", "4");
                    put("字段代码", "APPLICANT");
                    put("字段含义", "申请人");
                    put("数据类型", "VARCHAR");
                    put("长度", "64");
                    put("主键", " ");
                    put("主码", " ");
                }});
                add(new HashMap<String, String>() {{
                    put("字段编号", "5");
                    put("字段代码", "LEAVE_TYPE");
                    put("字段含义", "请假类型");
                    put("数据类型", "VARCHAR");
                    put("长度", "64");
                    put("主键", " ");
                    put("主码", " ");
                }});
                add(new HashMap<String, String>() {{
                    put("字段编号", "6");
                    put("字段代码", "REASON");
                    put("字段含义", "请假事因");
                    put("数据类型", "VARCHAR");
                    put("长度", "64");
                    put("主键", " ");
                    put("主码", " ");
                }});
                add(new HashMap<String, String>() {{
                    put("字段编号", "7");
                    put("字段代码", "BEGIN_TIME");
                    put("字段含义", "起始时间");
                    put("数据类型", "TIMESTAMP");
                    put("长度", "");
                    put("主键", " ");
                    put("主码", " ");
                }});
                add(new HashMap<String, String>() {{
                    put("字段编号", "8");
                    put("字段代码", "END_TIME");
                    put("字段含义", "结束时间");
                    put("数据类型", "TIMESTAMP");
                    put("长度", "");
                    put("主键", " ");
                    put("主码", " ");
                }});
                add(new HashMap<String, String>() {{
                    put("字段编号", "9");
                    put("字段代码", "INSERT_PERSON");
                    put("字段含义", "登记人");
                    put("数据类型", "VARCHAR");
                    put("长度", "64");
                    put("主键", " ");
                    put("主码", " ");
                }});
                add(new HashMap<String, String>() {{
                    put("字段编号", "10");
                    put("字段代码", "APPROVEDBY");
                    put("字段含义", "批准人");
                    put("数据类型", "VARCHAR");
                    put("长度", "64");
                    put("主键", " ");
                    put("主码", " ");
                }});
            }
        };
        //构建数据源中的key值
        String[] keysStrings = {"字段编号", "字段代码", "字段含义", "数据类型", "长度", "主键", "主码"};
        //每页的名称
        String[] sheetNameStrings = {"Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6"};
        String modelURLString = "C:\\model\\1.xls";
        try {
            ExcelUtil.ExcelByModel("测试模板导出", modelURLString, dataSourceList, response, sheetNameStrings, keysStrings, 6);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}